﻿
Imports System.Text.RegularExpressions
Public Class FrmAjoutDep

#Region "Champs"
    Dim position As Integer = 0
    Dim D As Integer = 0
    Dim idi, idc As Integer
    Dim debugId As Integer = 1
    Dim nom, pre, rue, cp, vil, telpor, telfixe, email, rencomp, idva, idva2 As String
    Public dtAp, dtDI, dtFI, dtLi As String
    Public typInterv, typPC, lieu, pb, mat, tra As String
    Dim IdCli As Integer
    Dim TypInt, lieuInt, TypePC As String
    Dim macnx As New PPE3.ClassMySqlConnect("853339_depanneur", "071064")
    Dim nbchamps As Integer = 0
    Public AdEmail As String
    Public test As Integer
    Dim sec As Byte

#End Region

    Private Sub FrmAjoutDep_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        'SeConnecter()
        'MaRequete = "SELECT * FROM client, intervention, date"
        'MaCommandeSpecialRequete.CommandText = MaRequete
        'MonRemplisseur.SelectCommand = MaCommandeSpecialRequete
        'MonRemplisseur.Fill(MonJeuDeDonnees, "ToutLesDepannages")
        'MaConnexion.Close()
    End Sub

    Private Sub FrmAjoutDep_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.MdiParent = FrmStart
        Me.Top = 0
        Me.Left = 0

        'MaRequete = "SELECT * FROM client, intervention, date, depannage"
        'MaCommandeSpecialRequete.CommandText = MaRequete
        'MonRemplisseur.SelectCommand = MaCommandeSpecialRequete
        'MonRemplisseur.Fill(MonJeuDeDonnees, "ToutLesDepannages")

        'Generer clé primaire pour le client

        ' Remplir la ComboBox NomClient avec les occurences
        'macnx.Remplir_ComboBox("SELECT Distinct NomClient , IdClient  FROM client GROUP BY NomClient ORDER BY NomClient asc", ComboBoxNomClient, "lesNomsClients", "NomClient", "IdClient")

        ' TextBoxIdClient.Text = GenererClePrimaire("idClient", "client")
        TextBoxIdClient.Text = macnx.Generer_Cle_Primaire("idClient", "client")

        'Generer clé primaire pour l'intervention 
        ' TextBoxIdIntervention.Text = GenererClePrimaire("idIntervention", "intervention")
        TextBoxIdIntervention.Text = macnx.Generer_Cle_Primaire("idIntervention", "intervention")
        'If TextBoxIdClient.Text <> "" And TextBoxIdIntervention.Text <> "" Then

        'Else
        '    TextBoxIdClient.Text = "1"
        '    TextBoxIdIntervention.Text = "1"
        'End If
            If (cbRecharge.Items.Count <> 0) Then
                cbRecharge.DataSource = Nothing
                cbRecharge.Items.Clear()

                macnx.viderVue("LesNomsClients")
            End If

        Dim requete As String = "SELECT Distinct NomClient  FROM client ORDER BY NomClient asc"
        macnx.Chargement_ComboBox(cbRecharge, requete, "LesNomsClients", "NomClient", "NomClient")

           
        If cbRecharge.Items.Count <> 0 Then
            TextBoxNomClient.AutoCompleteCustomSource.Clear()

            While (D < cbRecharge.Items.Count)

                Dim val As String = cbRecharge.Items(D).Item(0).ToString
                TextBoxNomClient.AutoCompleteCustomSource.Add(val)

                D = D + 1
            End While
        End If

    End Sub

    Private Sub btnImprimer_Click(sender As System.Object, e As System.EventArgs)
        PrintForm1.Print()
    End Sub

#Region "Fonction"

    Public Function ValidMail(ByVal adresse As String) As Boolean
        Dim myRegex As System.Text.RegularExpressions.Regex
        myRegex = New Regex("^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$")
        '([\w]+) ==> caractère alphanumérique apparaissant une fois ou plus
        Return myRegex.IsMatch(adresse) 'retourne true ou false selon la vérification
    End Function

    Private Sub TextBoxEmail_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBoxEmail.LostFocus

        If ValidMail(TextBoxEmail.Text) = False Then
            Me.ErrorProvider1.SetError(Me.TextBoxEmail, "L'adresse E-Mail est invalide : @ ou . malplacés ou absents !!!")
        Else
            Me.ErrorProvider1.SetError(Me.TextBoxEmail, "")
        End If
    End Sub
    Public Function RemplaceApostrophesSlash(ByRef unechaine As String) As String
        Dim ch As String
        ch = unechaine.Replace("'", "/")
        Return ch
    End Function


    Public Function RemplaceSlashApostrophes(ByRef unechaine As String) As String
        Dim ch As String
        ch = unechaine.Replace("/", "'")
        Return ch
    End Function

    Public Function RemplaceSlashApostrophesDataTable(ByRef lesdonnees As DataTable)
        Dim nblignes As Integer
        Dim nbcolonnes As Integer
        Dim test As String = ""
        nblignes = lesdonnees.Rows.Count
        nbcolonnes = lesdonnees.Columns.Count
        Dim i, j As Integer
        For i = 0 To nblignes - 1
            For j = 0 To nbcolonnes - 1
                'Attention le replace ne s'applique qu'aux type String : utilisation de la classe de base Object :
                If Object.ReferenceEquals(lesdonnees.Rows(i).Item(j).GetType(), test.GetType()) Then
                    lesdonnees.Rows(i).Item(j) = RemplaceSlashApostrophes(lesdonnees.Rows(i).Item(j))
                End If
            Next
        Next
        Return lesdonnees
    End Function
    Public Function ValidTelPort(ByVal adresse As String) As Boolean
        Dim myRegex As System.Text.RegularExpressions.Regex
        myRegex = New Regex("[0-9]{10}")

        Return myRegex.IsMatch(adresse) 'retourne true ou false selon la vérification
    End Function
    Private Sub TextBoxTelPort_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBoxTelPort.LostFocus
        If ValidTelPort(TextBoxTelPort.Text) = False Then
            Me.ErrorProvider1.SetError(Me.TextBoxTelPort, "Le Numéro de Téléphone n'est pas valide !!!")
        Else
            Me.ErrorProvider1.SetError(Me.TextBoxTelPort, "")
        End If
    End Sub
    Public Function ValidTelFixe(ByVal adresse As String) As Boolean
        Dim myRegex As System.Text.RegularExpressions.Regex
        myRegex = New Regex("[0-9]{10}")

        Return myRegex.IsMatch(adresse) 'retourne true ou false selon la vérification
    End Function
    Private Sub TextBoxTelFixe_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBoxTelFixe.LostFocus
        If ValidTelFixe(TextBoxTelPort.Text) = False Then
            Me.ErrorProvider1.SetError(Me.TextBoxTelFixe, "Le Numéro de Téléphone n'est pas valide !!!")
        Else
            Me.ErrorProvider1.SetError(Me.TextBoxTelFixe, "")
        End If
    End Sub

#End Region

    Private Sub BtnFermer_Click(sender As System.Object, e As System.EventArgs) Handles BtnFermer.Click
        Close()
    End Sub

    Private Sub BtnRecap_Click(sender As System.Object, e As System.EventArgs) Handles BtnRecap.Click
        If TextBoxCP.ReadOnly = True Then


            If BtnRecap.Text = "Ajout et Récapitulatif" Then
                
                DateTimePickerRDV.CustomFormat = "yyyy-MM-dd HH:mm:ss"
                idc = TextBoxIdClient.Text
                nom = RemplaceApostrophesSlash(TextBoxNomClient.Text)
                pre = ComboBoxPrenom.Text
                email = TextBoxEmail.Text
                rue = RemplaceApostrophesSlash(TextBoxAdresse.Text)
                vil = RemplaceApostrophesSlash(TextBoxVille.Text)
                cp = TextBoxCP.Text
                telpor = TextBoxTelPort.Text
                telfixe = TextBoxTelFixe.Text
                rencomp = RemplaceApostrophesSlash(TextBoxRenComp.Text)

                idi = TextBoxIdIntervention.Text
                'si le RadioButtonDepannage est activé
                If (RadioButtonDepannage.Checked) Then
                    typInterv = "depannage"

                End If
                'Si le RadioButtonFormation est activé
                If (RadioButtonFormation.Checked) Then
                    typInterv = "formation"

                End If
                'Si le RadioButtonteVente est activé
                If (RadioButtonVente.Checked) Then
                    typInterv = "vente"

                End If
                'Si le RadioButtonPortable est activé
                If (RadioButtonPortable.Checked) Then
                    typPC = "portable"
                    TextBoxAutre.Text = ""
                End If
                'Si le RadioButtonFixe est activé
                If (RadioButtonFixe.Checked) Then
                    typPC = "fixe"
                    TextBoxAutre.Enabled = True
                End If

                If (TextBoxAutre.Text <> "") Then
                    typPC = TextBoxAutre.Text
                    RadioButtonFixe.Checked = False
                    RadioButtonPortable.Checked = False
                End If

                If (RadioButtonDomicile.Checked) Then
                    lieu = "domicile"
                End If
                If (RadioButtonAtelier.Checked) Then
                    lieu = "atelier"
                End If
                If (RadioButtonDomAtel.Checked) Then
                    lieu = "Domicile & Atelier"
                End If

                pb = RemplaceApostrophesSlash(TextBoxPb.Text)
                mat = RemplaceApostrophesSlash(TextBoxMateriel.Text)
                tra = RemplaceApostrophesSlash(TextBoxTravaux.Text)

                Try

                    'requete inter into dans les tables clients, intervention, date, depannage

                    macnx.Se_Connecter_MySqlServer()

                    If (lieu = "domicile") Or (typInterv = "formation") Then
                        macnx.Executer_Requete_Action("Insert into intervention Values(" & idi & ",'" & typInterv & "', '" & typPC & "', '" & lieu & "', '" & pb & "', '" & mat & "', '" & tra & "'," & idc & ",'deplacement');Insert into date Values( NOW() , '" & DateTimePickerRDV.Text & "' , NULL , NULL , " & idc & " , " & idi & " );")

                    ElseIf (typInterv = "vente") Then
                        macnx.Executer_Requete_Action("Insert into intervention Values(" & idi & ",'" & typInterv & "', '" & typPC & "', '" & lieu & "', '" & pb & "', '" & mat & "', '" & tra & "'," & idc & ",'vente');Insert into date Values( NOW() , '" & DateTimePickerRDV.Text & "' , NULL , NULL , " & idc & " , " & idi & " );")

                    Else
                        macnx.Executer_Requete_Action("Insert into intervention Values(" & idi & ",'" & typInterv & "', '" & typPC & "', '" & lieu & "', '" & pb & "', '" & mat & "', '" & tra & "'," & idc & ",'en reception');Insert into date Values( NOW() , NULL , NULL , NULL , " & idc & " , " & idi & " );")

                    End If

                    macnx.Se_Deconnecter_MySqlServer()

                    MessageBox.Show("Bon Ajouté...!", "Alerte!", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    BtnRecap.Text = "Ajout et Récapitulatif"

                    If TextBoxEmail.Text <> "" Then
                    Else

                        AdEmail = InputBox("L'adresse email n'a pas été rempli!", "Attention", "pasdadressemail@email.fr")

                        If AdEmail <> "" Then
                            macnx.Se_Connecter_MySqlServer()
                            macnx.Executer_Requete_Action("update client set emailClient ='" & AdEmail & "' where idClient = " & TextBoxIdClient.Text & ";")
                            macnx.Se_Deconnecter_MySqlServer()


                        End If
                    End If
                Catch ex As Exception
                    MessageBox.Show(ex.Message)

                End Try
                test = 1
                FrmRecapAjoutDep.LblNom.Text = TextBoxNomClient.Text
                FrmRecapAjoutDep.LblPrenom.Text = ComboBoxPrenom.Text
                FrmRecapAjoutDep.create = True
                FrmTravauxEffectues.Show()
                FrmRecapAjoutDep.Show()

                Me.Close()

            End If

        Else
            MessageBox.Show("Aucun client de la base de donnée n'a été selectioné")
        End If
    End Sub

    Private Sub TextBoxIdIntervention_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBoxIdIntervention.TextChanged
        FrmRecapAjoutDep.lblIdInt.Text = TextBoxIdIntervention.Text
    End Sub

    Private Sub TextBoxAutre_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBoxAutre.TextChanged
        If TextBoxAutre.Text <> "" Then
            RadioButtonPortable.Checked = False
            RadioButtonFixe.Checked = False
        End If
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Close()
    End Sub


    Private Sub BtnRecherche_Click(sender As System.Object, e As System.EventArgs) Handles BtnRecherche.Click
        If (TextBoxNomClient.Text <> "") And (ComboBoxPrenom.Text <> "") Then


            Try
                idva2 = ComboBoxPrenom.Text
                macnx.viderVue("RechercheCli")
                DataGridViewRecherche.DataSource = macnx.Executer_Requete_Select("SELECT * FROM client WHERE ( NomClient LIKE '" & idva & "' ) AND ( PrenomClient LIKE '" & idva2 & "' )", "RechercheCli")
                If (DataGridViewRecherche.ColumnCount <> 0) Then
                    TextBoxIdClient.Text = DataGridViewRecherche.Rows(0).Cells(0).Value.ToString
                    TextBoxAdresse.Text = DataGridViewRecherche.Rows(0).Cells(3).Value.ToString
                    TextBoxCP.Text = DataGridViewRecherche.Rows(0).Cells(4).Value.ToString
                    TextBoxVille.Text = DataGridViewRecherche.Rows(0).Cells(5).Value.ToString
                    TextBoxTelPort.Text = DataGridViewRecherche.Rows(0).Cells(6).Value.ToString
                    TextBoxTelFixe.Text = DataGridViewRecherche.Rows(0).Cells(7).Value.ToString
                    TextBoxEmail.Text = DataGridViewRecherche.Rows(0).Cells(8).Value.ToString
                    TextBoxRenComp.Text = DataGridViewRecherche.Rows(0).Cells(9).Value.ToString

                    TextBoxAdresse.ReadOnly = True
                    TextBoxCP.ReadOnly = True
                    TextBoxVille.ReadOnly = True
                    TextBoxTelPort.ReadOnly = True
                    TextBoxTelFixe.ReadOnly = True
                    TextBoxEmail.ReadOnly = True
                    TextBoxRenComp.ReadOnly = True

                End If
            Catch ex As Exception
                MessageBox.Show("Désolé le client " & TextBoxNomClient.Text & " " & idva2 & " n'existe pas dans la Base de Donnée ! ", "Alerte!", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        Else
            MessageBox.Show("L'un des champs Nom , Prenom ou les deux n'a pas été rempli", "Alerte!", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub TextBoxNomClient_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBoxNomClient.TextChanged
        macnx.viderVue("lesPrenomsClients")
        If ComboBoxPrenom.Items.Count <> 0 Then
            ComboBoxPrenom.Items.Clear()
        End If


        idva = TextBoxNomClient.Text
        macnx.Chargement_ComboBox(ComboBoxPrenom, "SELECT PrenomClient , IdClient FROM client WHERE NomClient LIKE '" & idva & "' ORDER BY PrenomClient asc", "lesPrenomsClients", "IdClient", "PrenomClient")
        ComboBoxPrenom.Text = ""
        D = 0

        

        D = 0

        If ComboBoxPrenom.Items.Count <> 0 Then
            ComboBoxPrenom.AutoCompleteCustomSource.Clear()
            While (D < ComboBoxPrenom.Items.Count)

                Dim val2 As String = ComboBoxPrenom.Items(D).Item(0).ToString
                ComboBoxPrenom.AutoCompleteCustomSource.Add(val2)
                D = D + 1
            End While
        End If
    End Sub


        
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        If (TextBoxNomClient.Text <> "") And (ComboBoxPrenom.Text <> "") And (TextBoxAdresse.Text <> "") And (TextBoxCP.Text <> "") And (TextBoxVille.Text <> "") Then

            TextBoxIdClient.Text = macnx.Generer_Cle_Primaire("IdClient", "client")

            If BtnRecap.Text = "Ajout et Récapitulatif" Then

                DateTimePickerRDV.CustomFormat = "yyyy-MM-dd HH:mm:ss"
                idc = TextBoxIdClient.Text
                nom = RemplaceApostrophesSlash(TextBoxNomClient.Text)
                pre = ComboBoxPrenom.Text
                email = TextBoxEmail.Text
                rue = RemplaceApostrophesSlash(TextBoxAdresse.Text)
                vil = RemplaceApostrophesSlash(TextBoxVille.Text)
                cp = TextBoxCP.Text
                telpor = TextBoxTelPort.Text
                telfixe = TextBoxTelFixe.Text
                rencomp = RemplaceApostrophesSlash(TextBoxRenComp.Text)


                Try
                    If TextBoxTelFixe.Text = "" Then
                        TextBoxTelFixe.Text = "0444444444"
                    End If
                    If TextBoxTelPort.Text = "" Then
                        TextBoxTelPort.Text = "0666666666"
                    End If
                    'requete inter into dans les tables clients

                    macnx.Se_Connecter_MySqlServer()
                    macnx.Executer_Requete_Action("Insert into client Values( " & idc & ",'" & nom & "', '" & pre & "', '" & rue & "', '" & cp & "','" & vil & "', '" & telpor & "','" & telfixe & "', '" & email & "','" & rencomp & "')")
                    macnx.Se_Deconnecter_MySqlServer()

                    TextBoxAdresse.ReadOnly = True
                    TextBoxCP.ReadOnly = True
                    TextBoxVille.ReadOnly = True
                    TextBoxTelPort.ReadOnly = True
                    TextBoxTelFixe.ReadOnly = True
                    TextBoxEmail.ReadOnly = True
                    TextBoxRenComp.ReadOnly = True

                    MessageBox.Show("Client Ajouter", "Alerte!", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    BtnRecap.Text = "Ajout et Récapitulatif"
                    If TextBoxEmail.Text <> "" Then
                    Else

                        AdEmail = InputBox("L'adresse email n'a pas été rempli!", "Attention", "pasdadressemail@email.fr")

                        If AdEmail <> "" Then
                            macnx.Se_Connecter_MySqlServer()
                            macnx.Executer_Requete_Action("update client set emailClient ='" & AdEmail & "' where idClient = " & TextBoxIdClient.Text & ";")
                            macnx.Se_Deconnecter_MySqlServer()
                        End If
                    End If
                Catch ex As Exception
                    MessageBox.Show(ex.Message)

                End Try
            End If
        Else
            MessageBox.Show("Les information Nom , Prénom , Adresse , Ville et Code Postal doit ètre obligatoirement renseignées", "Alerte!", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub BtnModif_Click(sender As System.Object, e As System.EventArgs) Handles BtnModif.Click
        If TextBoxCP.ReadOnly = True Then


            If BtnModif.Text = "Modif" Then
                BtnModif.Text = "Valider!"
                BtnModif.BackColor = Color.Green
                TextBoxAdresse.ReadOnly = False
                TextBoxCP.ReadOnly = False
                TextBoxVille.ReadOnly = False
                TextBoxTelPort.ReadOnly = False
                TextBoxTelFixe.ReadOnly = False
                TextBoxEmail.ReadOnly = False
                TextBoxRenComp.ReadOnly = False
            End If

        ElseIf BtnModif.Text = "Valider!" Then
            Try

                If TextBoxNomClient.Text <> "" Then
                    idc = CInt(TextBoxIdClient.Text)
                    nom = RemplaceApostrophesSlash(TextBoxNomClient.Text)
                    pre = ComboBoxPrenom.Text
                    email = TextBoxEmail.Text
                    rue = RemplaceApostrophesSlash(TextBoxAdresse.Text)
                    vil = RemplaceApostrophesSlash(TextBoxVille.Text)
                    cp = TextBoxCP.Text
                    telpor = TextBoxTelPort.Text
                    telfixe = TextBoxTelFixe.Text
                    rencomp = RemplaceApostrophesSlash(TextBoxRenComp.Text)

                    macnx.Se_Connecter_MySqlServer()
                    macnx.Executer_Requete_Action("update Client Set NomClient = '" & nom & "', PrenomClient ='" & pre & "',  AdresseClient ='" & rue & "', CodePostalClient ='" & cp & "', VilleClient ='" & vil & "', TelPortClient = '" & telpor & "', TelFixeClient = '" & telfixe & "',  EmailClient = '" & email & "', RenseignementCompAdresse = '" & rencomp & "' where IdClient=" & idc & "")
                    macnx.Se_Deconnecter_MySqlServer()
                    MessageBox.Show("Information du Client Modifié...!", "Alerte!", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    position = 0
                    If TextBoxEmail.Text <> "" Then
                    Else

                        AdEmail = InputBox("L'adresse email n'a pas été rempli!", "Attention", "pasdadressemail@email.fr")

                        If AdEmail <> "" Then
                            macnx.Se_Connecter_MySqlServer()
                            macnx.Executer_Requete_Action("update client set emailClient ='" & AdEmail & "' where idClient = " & TextBoxIdClient.Text & ";")
                            macnx.Se_Deconnecter_MySqlServer()
                        End If
                    End If
                    BtnModif.Text = "Modif"
                    BtnModif.BackColor = Color.Red
                    TextBoxAdresse.ReadOnly = True
                    TextBoxCP.ReadOnly = True
                    TextBoxVille.ReadOnly = True
                    TextBoxTelPort.ReadOnly = True
                    TextBoxTelFixe.ReadOnly = True
                    TextBoxEmail.ReadOnly = True
                    TextBoxRenComp.ReadOnly = True


                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
    End Sub
End Class